Missing value treatment.

In [1]:
#Import modules.

import pandas as pd
import numpy as np
In [2]:
#Form the dataframe.
data = {'first_name': ['Jason', np.nan, 'Tina', 'Jake', 'Amy'], 
        'last_name': ['Miller', np.nan, 'Ali', 'Milner', 'Cooze'], 
        'age': [42, np.nan, 36, 24, 73], 
        'sex': ['m', np.nan, 'f', 'm', 'f'], 
        'preTestScore': [4, np.nan, np.nan, 2, 3],
        'postTestScore': [25, np.nan, np.nan, 62, 70]}
df = pd.DataFrame(data, columns = ['first_name', 'last_name', 'age', 'sex', 'preTestScore', 'postTestScore'])
df
Out[2]:
first_name last_name age sex preTestScore postTestScore
0 Jason Miller 42.0 m 4.0 25.0
1 NaN NaN NaN NaN NaN NaN
2 Tina Ali 36.0 f NaN NaN
3 Jake Milner 24.0 m 2.0 62.0
4 Amy Cooze 73.0 f 3.0 70.0

Drop missing observations

In [3]:
df_no_missing = df.dropna()
df_no_missing
Out[3]:
first_name last_name age sex preTestScore postTestScore
0 Jason Miller 42.0 m 4.0 25.0
3 Jake Milner 24.0 m 2.0 62.0
4 Amy Cooze 73.0 f 3.0 70.0

Drop those rows where all record in that row is NA

In [4]:
df_cleaned = df.dropna(how='all')
df_cleaned
Out[4]:
first_name last_name age sex preTestScore postTestScore
0 Jason Miller 42.0 m 4.0 25.0
2 Tina Ali 36.0 f NaN NaN
3 Jake Milner 24.0 m 2.0 62.0
4 Amy Cooze 73.0 f 3.0 70.0

Let's create a new column called 'Location' with full of missing values

In [5]:
df['location'] = np.nan
df
Out[5]:
first_name last_name age sex preTestScore postTestScore location
0 Jason Miller 42.0 m 4.0 25.0 NaN
1 NaN NaN NaN NaN NaN NaN NaN
2 Tina Ali 36.0 f NaN NaN NaN
3 Jake Milner 24.0 m 2.0 62.0 NaN
4 Amy Cooze 73.0 f 3.0 70.0 NaN

Drop those columnwho have a missing values

In [6]:
df.dropna(axis=1, how='all')
Out[6]:
first_name last_name age sex preTestScore postTestScore
0 Jason Miller 42.0 m 4.0 25.0
1 NaN NaN NaN NaN NaN NaN
2 Tina Ali 36.0 f NaN NaN
3 Jake Milner 24.0 m 2.0 62.0
4 Amy Cooze 73.0 f 3.0 70.0

Drop those rows that contain less than five observations

In [7]:
df.dropna(thresh=5)
Out[7]:
first_name last_name age sex preTestScore postTestScore location
0 Jason Miller 42.0 m 4.0 25.0 NaN
3 Jake Milner 24.0 m 2.0 62.0 NaN
4 Amy Cooze 73.0 f 3.0 70.0 NaN

Fill in missing data with zeros

In [8]:
df.fillna(0)
Out[8]:
first_name last_name age sex preTestScore postTestScore location
0 Jason Miller 42.0 m 4.0 25.0 0.0
1 0 0 0.0 0 0.0 0.0 0.0
2 Tina Ali 36.0 f 0.0 0.0 0.0
3 Jake Milner 24.0 m 2.0 62.0 0.0
4 Amy Cooze 73.0 f 3.0 70.0 0.0

Fill the missing values with mean()

In [9]:
df["preTestScore"].fillna(df["preTestScore"].mean(), inplace=True)
df
Out[9]:
first_name last_name age sex preTestScore postTestScore location
0 Jason Miller 42.0 m 4.0 25.0 NaN
1 NaN NaN NaN NaN 3.0 NaN NaN
2 Tina Ali 36.0 f 3.0 NaN NaN
3 Jake Milner 24.0 m 2.0 62.0 NaN
4 Amy Cooze 73.0 f 3.0 70.0 NaN
In [10]:
df["postTestScore"].fillna(df.groupby("sex")["postTestScore"].transform("mean"), inplace=True)
df
Out[10]:
first_name last_name age sex preTestScore postTestScore location
0 Jason Miller 42.0 m 4.0 25.0 NaN
1 NaN NaN NaN NaN 3.0 NaN NaN
2 Tina Ali 36.0 f 3.0 70.0 NaN
3 Jake Milner 24.0 m 2.0 62.0 NaN
4 Amy Cooze 73.0 f 3.0 70.0 NaN
In [11]:
# Select the rows of df where age is not NaN and sex is not NaN
df[df['age'].notnull() & df['sex'].notnull()]
Out[11]:
first_name last_name age sex preTestScore postTestScore location
0 Jason Miller 42.0 m 4.0 25.0 NaN
2 Tina Ali 36.0 f 3.0 70.0 NaN
3 Jake Milner 24.0 m 2.0 62.0 NaN
4 Amy Cooze 73.0 f 3.0 70.0 NaN

How Replace() work

In [12]:
#Lets create a new data set to explore replace functions
data = pd.Series([1,2,-99,4,5,-99,7,8,-99])
data
Out[12]:
0     1
1     2
2   -99
3     4
4     5
5   -99
6     7
7     8
8   -99
dtype: int64
In [13]:
# Replace the placeholder -99 as NaN
data.replace(-99, np.nan)
Out[13]:
0    1.0
1    2.0
2    NaN
3    4.0
4    5.0
5    NaN
6    7.0
7    8.0
8    NaN
dtype: float64

You will no longer see the -99, because it is replaced by NaN and hence not shown.

In [ ]:

Happy learning....

In [ ]: